package jdbc.datasource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @author DCSGO
 * @version 1.0
 * apache-DBUtils 的使用
 */
public class DBUtils_USE {
    @Test
    public void testUseDBUtils() throws SQLException {
        //前置工作
        //1.导入 apache-DBUtils 的jar文件到libs中
        //2.将jar文件添加到库

        //1.获取连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2.创建 QueryRunner 对象
        QueryRunner queryRunner = new QueryRunner();

        //3.获取查询结果
        //public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {}
        String sql = "select * from performer where id >= ?";
        //1) conn 与数据库建立的连接
        //2) sql 要执行的sql语句可以包含?
        //3) rsh
        //   将 resultSet 中的数据映射成 Performer 对象保存到一个 List 中并返回
        //   底层使用反射机制去获取 Performer 类的属性，然后进行封装
        //4) params sql 语句中的占位符(是一个可变参数)
        List<Performer> list = queryRunner.query(connection, sql, new BeanListHandler<>(Performer.class), 1);
        /*
        public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
            PreparedStatement stmt = null;//创建一个PreparedStatement对象
            ResultSet rs = null;//创建一个ResultSet对象
            Object result = null;//返回的结果 即List

            try {
                stmt = this.prepareStatement(conn, sql);//通过传入的conn获取prepareStatement
                this.fillStatement(stmt, params);//替换占位符
                rs = this.wrap(stmt.executeQuery());
                result = rsh.handle(rs);//将结果集rs中的数据封装到一个List集合中并赋给 result
            } catch (SQLException var33) {
                this.rethrow(var33, sql, params);
            } finally {
                try {
                    this.close(rs);//关闭结果集 rs
                } finally {
                    this.close((Statement)stmt);//关闭prepareStatement stmt
                }
            }

            return result;//将结果集中封装好的结果返回
        }
         */

        for (Performer p : list) {
            System.out.print(p);
        }

        System.out.println("\n----------------------------------------------");

        sql = "select `name`,phone_number from performer";
        List<Performer> list1 = queryRunner.query(connection, sql, new BeanListHandler<>(Performer.class));
        for (Performer p : list1) {
            System.out.print(p);
        }

        /*
         * 十月 03, 2023 11:23:24 上午 com.alibaba.druid.pool.DruidDataSource info
         * 信息: {dataSource-1} inited
         *
         * Performer{id=1, name='郭富城', sex='男', birthday=1990-04-25, phone_number='11223344556'}
         * Performer{id=6, name='周星驰', sex='男', birthday=1970-01-01, phone_number='12345678910'}
         * ----------------------------------------------
         *
         * Performer{id=null, name='郭富城', sex='null', birthday=null, phone_number='11223344556'}
         * Performer{id=null, name='周星驰', sex='null', birthday=null, phone_number='12345678910'}
         * 进程已结束，退出代码为 0
         */
        JDBCUtilsByDruid.close(null, null, connection);
    }

    //查询结果为单行多列的情况
    @Test
    public void testQuerySingle() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from performer where id = ?";
        //我们只需要将第三个参数 rsh 改变一成 BeanHandler 就可以了，此时返回的是 Performer
        Performer performer = queryRunner.query(connection, sql, new BeanHandler<>(Performer.class), 1);
        System.out.println(performer);
        JDBCUtilsByDruid.close(null, null, connection);
        /*
         * 十月 04, 2023 8:10:19 上午 com.alibaba.druid.pool.DruidDataSource info
         * 信息: {dataSource-1} inited
         *
         * Performer{id=1, name='郭富城', sex='男', birthday=1990-04-25, phone_number='11223344556'}
         *
         * 进程已结束，退出代码为 0
         */
        //查询结果为空 则返回 null
    }

    //查询结果为单行单列的情况
    @Test
    public void testScalar() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select name from performer where id = ?";
        //将第三个参数改为 ScalarHandler 就可以了，此时返回的是 Object
        Object name = queryRunner.query(connection, sql, new ScalarHandler(), 1);
        System.out.println(name);
        JDBCUtilsByDruid.close(null, null, connection);
        /*
         * 十月 04, 2023 8:16:25 上午 com.alibaba.druid.pool.DruidDataSource info
         * 信息: {dataSource-1} inited
         * 郭富城
         *
         * 进程已结束，退出代码为 0
         */
        //查询结果为空 则返回 null
    }

    public void showPerformers() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String allData = "select * from performer";
        List<Performer> performers = queryRunner.query(connection, allData, new BeanListHandler<>(Performer.class));
        System.out.println("current performers:");
        for (Performer p : performers) {
            System.out.print(p);
        }
        System.out.println();
        JDBCUtilsByDruid.close(null, null, connection);
    }

    //apache-DBUtils DML
    @Test
    public void testDML() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        //insert
        String sql = "insert into performer values (null,?,?,?,?)";
        int affectedRows = queryRunner.update(connection, sql, "程小时", "男", "2000-01-01", "78945612309");
        //返回值为表中受影响的行数
        System.out.println("insert affectRows: " + affectedRows);
        showPerformers();

        //update
        sql = "update performer set name = ? where id = ?";
        affectedRows = queryRunner.update(connection, sql, "陆光", 1);
        System.out.println("update affectRows: " + affectedRows);
        showPerformers();

        //delete
        sql = "delete from performer where id = ?";
        affectedRows = queryRunner.update(connection, sql, 6);
        System.out.println("delete affectRows: " + affectedRows);
        showPerformers();

        JDBCUtilsByDruid.close(null, null, connection);
        /*
         * 十月 04, 2023 9:03:38 上午 com.alibaba.druid.pool.DruidDataSource info
         * 信息: {dataSource-1} inited
         * insert affectRows: 1
         * current performers:
         *
         * Performer{id=1, name='郭富城', sex='男', birthday=1990-04-25, phone_number='11223344556'}
         * Performer{id=6, name='周星驰', sex='男', birthday=1970-01-01, phone_number='12345678910'}
         * Performer{id=7, name='程小时', sex='男', birthday=2000-01-01, phone_number='78945612309'}
         * update affectRows: 1
         * current performers:
         *
         * Performer{id=1, name='陆光', sex='男', birthday=1990-04-25, phone_number='11223344556'}
         * Performer{id=6, name='周星驰', sex='男', birthday=1970-01-01, phone_number='12345678910'}
         * Performer{id=7, name='程小时', sex='男', birthday=2000-01-01, phone_number='78945612309'}
         * delete affectRows: 1
         * current performers:
         *
         * Performer{id=1, name='陆光', sex='男', birthday=1990-04-25, phone_number='11223344556'}
         * Performer{id=7, name='程小时', sex='男', birthday=2000-01-01, phone_number='78945612309'}
         *
         * 进程已结束，退出代码为 0
         */
    }
}
